Introduction to LOD Expressions in Tableau

Course: Tableau Training

Topic: Introduction to Level of Detail (LOD) Expressions

Course Link: JustAcademy Tableau Training

Demo Link: Register for a Tableau Course Demo

1. Introduction to LOD Expressions

LOD stands for Level of Detail. Level of Detail (LOD) expressions in Tableau allow analysts to specify the level of granularity at which a calculation should be performed. They are especially useful when the required calculation level differs from the level of detail currently displayed in a visualization.

For example, a chart may display sales by region, but an analyst may also need to calculate total sales for each customer, the first order date for each customer, or the average sales per customer within each region. LOD expressions help perform these calculations without requiring every dimension used in the calculation to appear in the visualization.

LOD expressions are useful for calculations that involve multiple levels of granularity in the same analysis. Tableau provides three main types: FIXED, INCLUDE, and EXCLUDE. It also supports table-scoped LOD expressions. Read Tableau's official LOD expressions documentation.

Simple example

{FIXED [Region] : SUM([Sales])}

This calculation computes the total sales for each region, regardless of which additional dimensions are present in the view. If the visualization also contains Category, the region-level result can be repeated across the categories belonging to that region.

2. What Does Level of Detail Mean?

Level of detail describes how much information is represented by each mark or result in a visualization. The level is determined by the dimensions used in the view, such as Region, Category, Customer Name or Order Date.

Example: Different levels of detail

The more dimensions that distinguish the marks, the more detailed the view generally becomes. The fewer dimensions used, the more aggregated the view becomes.

3. Why Are LOD Expressions Needed?

In Tableau, the same measure can produce different results depending on how data is grouped in the visualization. Sometimes, a business question requires a calculation at a different level from the visible chart.

Common problems solved by LOD expressions

• Calculating customer-level sales while displaying regional totals.
• Finding each customer's first order date.
• Calculating average sales per customer by region.
• Comparing regional sales with overall sales.
• Calculating order totals when the underlying data contains multiple order-detail rows.
• Finding the highest or lowest value for each group.
• Calculating the percentage contribution of a region to a larger total.
• Separating a calculation's granularity from the dimensions displayed in a chart.

Example without LOD

Suppose a chart displays total sales by region. You want to compare each region with each customer's total sales, but Customer Name is not in the view. A normal aggregation may calculate sales at the view's level of detail rather than the customer level required for the analysis.

Example with LOD

{FIXED [Customer Name] : SUM([Sales])}

This calculates sales for each customer independently of the dimensions in the view. You can then use the resulting customer-level values in an appropriate visualization or further aggregation.

4. Basic Syntax of LOD Expressions

A typical LOD expression has the following structure:

{ FIXED | INCLUDE | EXCLUDE [Dimension] : AGGREGATION([Measure]) }

The expression is enclosed in curly braces. The scope keyword determines how the calculation's granularity relates to the dimensions in the visualization.

Multiple dimensions

You can specify more than one dimension, separated by commas.

{FIXED [Region], [Category] : SUM([Sales])}

This expression calculates total sales at the combination of Region and Category.

5. Understanding the Three Main Types of LOD Expressions

6. FIXED LOD Expressions

A FIXED LOD expression calculates a value using the dimensions specified inside the expression, independently of the dimensions in the visualization. The calculation can be more detailed than, less detailed than, or at the same level as the view.

Syntax

{FIXED [Dimension] : AGGREGATION([Measure])}

Example 1: Sales by Region

{FIXED [Region] : SUM([Sales])}

Explanation: Tableau calculates total sales for each region. If the view includes Region and Category, the regional total can be repeated for each category within that region.

Example 2: Sales by Customer

{FIXED [Customer Name] : SUM([Sales])}

This expression calculates total sales for each customer, regardless of whether Customer Name appears in the visualization.

Example 3: Overall Sales Total

{FIXED : SUM([Sales])}

Because no dimensions are specified, the expression calculates a single overall sales total for the data considered by the calculation's filter context.

Example 4: First Order Date for Each Customer

{FIXED [Customer Name] : MIN([Order Date])}

This expression returns the earliest order date for each customer. It can be used to identify first-time customers or create customer cohorts based on their first order date.

Practical uses of FIXED

• Calculate customer-level or order-level metrics.
• Calculate an overall total for percentage-of-total analysis.
• Find the first or last date for each customer.
• Create a value that stays consistent when the dimensions in the view change.
• Build customer cohort and repeat-purchase analyses.

7. INCLUDE LOD Expressions

An INCLUDE LOD expression adds one or more specified dimensions to the level of detail already present in the view for the purpose of the calculation. This is particularly useful when you need a finer-grained result and then want to aggregate it at a higher level.

Syntax

{INCLUDE [Dimension] : AGGREGATION([Measure])}

Example 1: Customer Sales

{INCLUDE [Customer Name] : SUM([Sales])}

This expression computes sales at the customer level in addition to the dimensions already present in the view.

Example 2: Average Customer Sales by Region

Create a calculated field named Customer Sales:

{INCLUDE [Customer Name] : SUM([Sales])}

Place Region on Columns and Customer Sales on Rows. Change the aggregation of Customer Sales to AVG when the goal is to calculate the average customer-level sales within each region.

Explanation: Tableau calculates sales for each customer within the relevant view context and then averages those customer-level totals by region.

Example 3: Sales by State

{INCLUDE [State] : SUM([Sales])}

This expression includes State in the calculation even when State is not displayed in the view. It can be useful when a report needs to average state-level sales across another grouping, such as Category.

Practical uses of INCLUDE

• Calculate the average sales per customer by region.
• Calculate totals at a finer level before summarizing them.
• Analyze order-level results in a chart grouped by a broader dimension.
• Calculate metrics that need to respond to the dimensions in the view.

8. EXCLUDE LOD Expressions

An EXCLUDE LOD expression removes one or more dimensions from the view's level of detail for the calculation. It is useful when the chart displays detailed information but the calculation must ignore one of those dimensions.

Syntax

{EXCLUDE [Dimension] : AGGREGATION([Measure])}

Example 1: Sales Without Category Detail

{EXCLUDE [Category] : SUM([Sales])}

If the view displays Region and Category, this calculation removes Category from the calculation's level of detail. The resulting value represents sales at the remaining view level, such as Region, rather than separate category-level sales.

Example 2: Sales Without Region Detail

{EXCLUDE [Region] : SUM([Sales])}

This expression excludes Region from the view's level of detail for the calculation. If the view displays Region and Month, the result can represent the total sales for each month across regions.

Example 3: Compare Category Sales with Overall Category Total

When the view contains Category, an EXCLUDE calculation can provide a total that ignores Category while retaining other view dimensions.

{EXCLUDE [Category] : SUM([Sales])}

This can support comparison between category sales and a broader total. The exact result depends on the other dimensions present in the view.

Practical uses of EXCLUDE

• Compare detailed values with a broader total.
• Support percent-of-total calculations.
• Show an overall value alongside more detailed marks.
• Remove a dimension's influence from a calculation without removing it from the visualization.

9. Table-Scoped LOD Expressions

A table-scoped LOD expression calculates an aggregate without using the FIXED, INCLUDE or EXCLUDE keyword.

Syntax

{AGGREGATION([Field])}

Example: Earliest Order Date

{MIN([Order Date])}

This returns the earliest order date across the table's relevant data scope. It is equivalent in intent to a FIXED LOD expression with no dimension declaration:

{FIXED : MIN([Order Date])}

Another example: Overall Average Sales

{AVG([Sales])}

This calculates the average Sales value across the underlying records in the relevant data scope. Be careful to distinguish the average of individual records from the average of grouped totals; they answer different questions.

10. Understanding Granularity: Finer, Coarser and Same Level

LOD calculations and the view may operate at different levels of detail. Understanding the relationship helps explain why a value may repeat or why Tableau may aggregate a calculated field when it is placed in a view.

Example: Coarser LOD

{FIXED [Region] : SUM([Sales])}

If the view contains Region and Category, the same regional sales value may appear for each category within the region. This is expected because the expression is calculated at the region level.

Example: Finer LOD

{FIXED [Customer Name] : SUM([Sales])}

If the view displays only Region, Tableau must reconcile the customer-level results with the regional view. The aggregation applied to the LOD field determines how those values are summarized. Select SUM, AVG or another aggregation according to the business question, not merely to remove a display issue.

11. LOD Expressions and Filters

LOD calculations interact with Tableau's order of operations. This is especially important when a filter changes what users see but the LOD calculation continues to use a broader data scope.

General filter behavior

• Extract filters: Restrict data included in an extract.
• Data source filters: Restrict the records available from the data source.
• Context filters: Define a context that is considered before FIXED LOD calculations.
• Dimension filters: Generally affect INCLUDE and EXCLUDE calculations, but a FIXED LOD can be computed before ordinary dimension filters.
• Measure filters: Filter aggregated results in the view.
• Table calculation filters: Operate after table calculations and hide marks rather than removing the underlying data from those calculations.

Example: FIXED and a Region Filter

Suppose you create the following calculation:

{FIXED : SUM([Sales])}

You then add Region to Filters and select only West. A FIXED expression may still calculate the total using all regions because ordinary dimension filters are evaluated after FIXED LOD calculations.

If the FIXED calculation should respect the selected region filter, consider making the filter a context filter or using a different calculation approach. Context filters are evaluated before FIXED LOD calculations. Read Tableau's official explanation of LOD expressions and filters.

12. LOD Expressions vs. Regular Calculated Fields

13. LOD Expressions vs. Table Calculations

LOD expressions and table calculations can both help with advanced analysis, but they work at different stages and answer different kinds of questions.

14. Step-by-Step Practical Exercise: Sales by Region and Customer

Objective: Calculate customer-level sales while displaying a visualization grouped by Region.

Step 1: Connect to a sales dataset

1. Open Tableau.
2. Connect to a dataset containing Region, Customer Name and Sales.
3. Open a new worksheet.

Step 2: Create a FIXED calculation

Open Create Calculated Field and name the field Customer Sales.

{FIXED [Customer Name] : SUM([Sales])}

Step 3: Build a view

1. Drag Region to Columns.
2. Drag Customer Sales to Rows.
3. Choose an appropriate aggregation for the business question.
4. Compare the result with a view that uses Customer Name directly.

Step 4: Test the level of detail

1. Add Category to the view.
2. Observe how the customer-level result behaves across the additional dimension.
3. Compare the FIXED result with a normal SUM of Sales.
4. Remove Category and check the visualization again.

Expected learning: A FIXED LOD calculation can preserve customer-level calculations even when the dimensions in the view change. However, how Tableau aggregates or repeats those results depends on the relationship between the LOD granularity and the view.

15. Practical Exercise: Average Sales per Customer by Region

Objective: Find average customer-level sales within each region.

Step 1: Create a calculated field

{INCLUDE [Customer Name] : SUM([Sales])}

Name the field Customer Sales Included.

Step 2: Build the visualization

1. Drag Region to Columns.
2. Drag Customer Sales Included to Rows.
3. Change the field's aggregation to AVG when the goal is the average of customer-level totals.
4. Format the result as currency if appropriate.

Step 3: Interpret the result

The visualization averages customer-level sales within the relevant region. This differs from total regional sales divided by an arbitrary number of records. The result depends on the customer-level calculation and the dimensions present in the view.

16. Practical Exercise: Compare Category Sales with a Broader Total

Objective: Show category-level sales alongside a calculation that excludes Category from the view's level of detail.

Step 1: Create the calculation

{EXCLUDE [Category] : SUM([Sales])}

Name it Sales Without Category.

Step 2: Build the visualization

1. Drag Region and Category into the view.
2. Add SUM(Sales) to Text or Rows to show category sales.
3. Add Sales Without Category to the view.
4. Compare the category-level sales with the broader regional total.

Expected learning: EXCLUDE removes Category from the calculation's view-level granularity without requiring Category to be removed from the chart itself.

17. Common Errors and Troubleshooting

18. Best Practices for LOD Expressions

• Understand the business question before selecting FIXED, INCLUDE or EXCLUDE.
• Write down the intended calculation level, such as customer, order, region or month.
• Use FIXED when you need a calculation based on explicitly specified dimensions, independently of the dimensions in the view.
• Use INCLUDE when you need to add a finer level of detail to the view's calculation.
• Use EXCLUDE when you need to remove a dimension from the view's level of detail for a calculation.
• Use table-scoped LOD expressions when the calculation needs an overall aggregate without a dimension declaration.
• Check how the LOD field is aggregated when added to a visualization.
• Understand filter order, particularly the difference between FIXED and INCLUDE/EXCLUDE.
• Use descriptive calculated-field names, such as Customer Sales or First Order Date.
• Test calculations in a simple worksheet before using them in a complex dashboard.
• Validate averages and totals carefully, especially when averaging already-aggregated values.
• Keep only the necessary LOD calculations in complex reports and verify performance.

19. Practice Questions

1. What does LOD stand for in Tableau?
2. What is the purpose of an LOD expression?
3. What are the three main types of LOD expressions?
4. Write an expression to calculate sales by Region.
5. Write an expression to calculate sales by Customer Name.
6. How does FIXED differ from INCLUDE?
7. How does EXCLUDE work?
8. Why might a FIXED calculation ignore a normal dimension filter?
9. What is a table-scoped LOD expression?
10. When might you use an LOD expression instead of a table calculation?

20. Practice Questions: Answers

Answer 1

LOD stands for Level of Detail.

Answer 2

An LOD expression lets you control the granularity at which an aggregation is calculated, independently of or in relation to the visualization's level of detail.

Answer 3

The three main types are FIXED, INCLUDE and EXCLUDE.

Answer 4

{FIXED [Region] : SUM([Sales])}

Answer 5

{FIXED [Customer Name] : SUM([Sales])}

Answer 6

FIXED calculates using the dimensions explicitly specified in the expression. INCLUDE adds a dimension to the dimensions already present in the view for the calculation.

Answer 7

EXCLUDE removes the specified dimension from the view's level of detail for the calculation.

Answer 8

FIXED LOD calculations are evaluated before ordinary dimension filters. Context, data source and extract filters are evaluated earlier and can affect the calculation.

Answer 9

A table-scoped LOD expression performs an aggregate without a FIXED, INCLUDE or EXCLUDE keyword, such as {MIN([Order Date])}.

Answer 10

Use an LOD expression when the required calculation needs a specified data granularity, such as customer-level sales within a regional visualization. Use table calculations for analyses such as running totals and ranks over the displayed results.

21. Interview Questions and Answers

Q1. What are LOD expressions in Tableau?

LOD expressions are calculations that let you specify the level of granularity at which an aggregate should be computed.

Q2. Why are LOD expressions important?

They allow analysts to calculate values at a level that differs from the dimensions displayed in a visualization.

Q3. What is FIXED LOD?

FIXED computes a value using the dimensions explicitly declared in the expression, independently of the dimensions in the view.

Q4. What is INCLUDE LOD?

INCLUDE adds specified dimensions to the view's level of detail for the calculation.

Q5. What is EXCLUDE LOD?

EXCLUDE removes specified dimensions from the view's level of detail for the calculation.

Q6. Can an LOD expression contain multiple dimensions?

Yes. Multiple dimensions can be specified, separated by commas, for example {FIXED [Region], [Category] : SUM([Sales])}.

Q7. Why does a FIXED LOD value sometimes repeat across categories?

The expression may calculate at Region level while the view contains both Region and Category. The region-level result can therefore be repeated across categories within the region.

Q8. How do LOD expressions interact with filters?

FIXED is evaluated before ordinary dimension filters, while INCLUDE and EXCLUDE are evaluated after dimension filters. Extract, data source and context filters can affect FIXED calculations.

Q9. Can LOD expressions be nested?

Tableau supports nested LOD expressions in appropriate calculations. The inner and outer expressions must be designed carefully so that their granularities and aggregations answer the intended question.

Q10. What is the difference between LOD expressions and table calculations?

LOD expressions define calculation granularity using data dimensions, while table calculations operate on the aggregated results represented in the visualization.

22. Quick Revision Checklist

• Understand granularity and the level of detail in a visualization.
• Learn the syntax of LOD expressions.
• Understand FIXED, INCLUDE and EXCLUDE.
• Practice table-scoped LOD expressions.
• Calculate sales by region and customer.
• Calculate average customer sales by region.
• Use EXCLUDE to compare detailed values with broader totals.
• Understand how dimensions in the view affect INCLUDE and EXCLUDE.
• Understand how filter order affects FIXED calculations.
• Check the aggregation of LOD fields in the view.
• Compare LOD expressions with regular calculations and table calculations.
• Test calculations with small examples before using them in dashboards.

23. Conclusion

LOD expressions are an essential Tableau feature for advanced data analysis. They allow calculations to be performed at a specified level of granularity, making it possible to analyze customer-level, order-level, regional and overall metrics in the same report.

Remember the basic distinction: FIXED uses explicitly declared dimensions, INCLUDE adds dimensions to the view's level of detail for the calculation, and EXCLUDE removes dimensions from the view's level of detail for the calculation. Choosing the right expression depends on the business question, the dimensions in the view, the aggregation required and the filter behavior.

24. Learning Resources

• JustAcademy Tableau Training — Explore the Tableau training course.
• Register for a Tableau Course Demo — Register for a course demo.
• Tableau Official Documentation: Create LOD Expressions
• Tableau Official Documentation: How LOD Expressions Work
• Tableau Official Documentation: EXCLUDE LOD Expressions
• Tableau Whitepaper: Understanding LOD Expressions